﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

using Mysqlx.Crud;

namespace student_systemApp
{
    public partial class ListClassForm : Form
    {
        public ListClassForm()
        {
            InitializeComponent();
        }

        private void ListClass_Load(object sender, EventArgs e)
        {
            //加载年级下拉框
            initGrades();
            //加载班级信息
            loadClassInfo();
        }

        private void loadClassInfo()
        {
            string connectionString = ConfigurationManager.ConnectionStrings["UserManagementConnectionString"].ConnectionString;
            string sql = "SELECT class_id,class_name,grade_name,remark FROM class " +
                "inner join grade on class.grade_id = grade.grade_id";
            //" WHERE (@className IS NULL OR class_name like @className)" +
            //" AND (@gradeId IS NULL OR class.grade_id = @gradeId)" +
            // 构建动态WHERE子句
            List<string> conditions = new List<string>();
            if (comboBox_grade.SelectedIndex > 0)
            {
                conditions.Add("class.grade_id = @gradeId");
            }
            if (!string.IsNullOrEmpty(textBox_className.Text.Trim()))
            {
                conditions.Add("class_name LIKE @className");
            }
            // 如果有条件，则添加WHERE子句
            if (conditions.Count > 0)
            {
                sql += " WHERE " + string.Join(" AND ", conditions);
            }

            sql += " ORDER BY class_id ASC;";
            using (var connection = new MySqlConnection(connectionString))
            {
                try
                { 
                    connection.Open();
                    MySqlCommand command = new MySqlCommand(sql, connection);
                    command.Parameters.AddWithValue("@className", "%" + textBox_className.Text + "%");
                    command.Parameters.AddWithValue("@gradeId", comboBox_grade.SelectedValue);


                    var adapter = new MySqlDataAdapter(command);
                    var dataTable = new DataTable();
                    adapter.Fill(dataTable);
                    // 将 DataTable 绑定到 DataGridView 控件
                    dgv_listClass.DataSource = dataTable;
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    MessageBox.Show("查找失败..." + ex.Message, "提示");
                }
                connection.Close();
            }
        }

        public void initGrades()
        {
            string connectionString = ConfigurationManager.ConnectionStrings["UserManagementConnectionString"].ConnectionString;
            string sql = "SELECT grade_id,grade_name FROM grade;";

            using (var connection = new MySqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    MySqlCommand command = new MySqlCommand(sql, connection);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(command);
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);

                    DataRow row = dt.NewRow();
                    row["grade_name"] = "请选择";
                    row["grade_id"] = 0;
                    dt.Rows.InsertAt(row, 0);

                    //绑定数据
                    comboBox_grade.DataSource = dt;
                    comboBox_grade.DisplayMember = "grade_name";
                    comboBox_grade.ValueMember = "grade_id";

                    //默认选择0
                    comboBox_grade.SelectedIndex = 0;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("查找失败" + ex.Message, "提示");
                }
                connection.Close();
            }
        }

        private void dgv_listClass_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void button_find_Click(object sender, EventArgs e)
        {
            loadClassInfo();
        }

        private void comboBox_grade_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void textBox_className_TextChanged(object sender, EventArgs e)
        {

        }
    }
}
